package common.dao;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.orm.hibernate4.support.HibernateDaoSupport;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class BaseDao {
	@Autowired
	public SessionFactory sessionFactory;
	@Autowired
	public HttpServletRequest request;
	
	Connection conn;
	Statement ps;
	{
		try {
			conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public boolean insertOrUpdate(String sql){
		try {
			ps=conn.createStatement();
			ps.executeUpdate(sql);
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return true;
	}
	
	public List<Map<String,Object>> selectSQL(String sql){
		try {
			ps=conn.createStatement();
			ResultSet rs=ps.executeQuery(sql);
			ResultSetMetaData m=rs.getMetaData();
			List<Map<String,Object>> list=new ArrayList<Map<String,Object>>(); 
			while(rs.next()){
				Map<String,Object> item=new HashMap<String, Object>();
				for(int i=1;i<=m.getColumnCount();i++){
					item.put(m.getColumnName(i), rs.getString(i));
				}
				list.add(item);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public List<Map<String,Object>> select(String src,Map<String,String> map){
		String basePath=request.getRealPath("/WEB-INF/classes/");
		Connection conn;
		try {
			conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			PreparedStatement ps=conn.prepareStatement(getSQL(basePath+src,map));
			ResultSet rs=ps.executeQuery();
			ResultSetMetaData m=rs.getMetaData();
			List<Map<String,Object>> list=new ArrayList<Map<String,Object>>(); 
			while(rs.next()){
				Map<String,Object> item=new HashMap<String, Object>();
				for(int i=1;i<=m.getColumnCount();i++){
					item.put(m.getColumnName(i), rs.getString(i));
				}
				list.add(item);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static String getSQL(String src,Map<String,String> map){
		
		src=src.replace("/", "\\")+".xml";
		System.out.println(src);
		DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
		try {
			String sql="";
			DocumentBuilder builder=factory.newDocumentBuilder();
			Document doc;
			doc=builder.parse(new File(src));
			NodeList root=doc.getElementsByTagName("query");
			NodeList query=root.item(0).getChildNodes();
			
			for(int i=0;i<query.getLength();i++){
				if(query.item(i).getNodeValue()!=null){
					sql+=" "+query.item(i).getNodeValue().trim();
				}else{
					Element element=(Element) query.item(i);
					if(element.getNodeName().equals("where")){
						NodeList where=element.getChildNodes();
						sql+=" where 1=1 ";
						for(int j=0;j<where.getLength();j++){
							if(where.item(j).getNodeValue()!=null){
								sql+=" "+where.item(j).getNodeValue().trim();
							}else{
								Element isNotEmpty=(Element) where.item(j);
								NodeList isNot=isNotEmpty.getChildNodes();
								String prepend=isNotEmpty.getAttribute("prepend");
								String property=isNotEmpty.getAttribute("property");
								System.out.println(map.get(property));
								if(map.get(property)!=null){
									String str=isNot.item(0).getNodeValue().trim();	
									String st=str.replace("#"+property+"#", map.get(property).toString());
									sql+=prepend+" "+st;
								}
							}
						}
					}else if(element.getNodeName().equals("isNotEmpty")){
						NodeList isNot=element.getChildNodes();
						String prepend=element.getAttribute("prepend");
						String property=element.getAttribute("property");
						if(map.get(property)!=null){
							String str=isNot.item(0).getNodeValue().trim();	
							String st=str.replace("#"+property+"#", map.get(property).toString());
							sql+=prepend+" "+st;
						}
					}
				}
			}
			System.out.println(sql);
			for(String key:map.keySet()){
				if(map.get(key)==null) continue;
				sql=sql.replace("#"+key+"#", map.get(key).toString());
			}
			return sql;
			
		} catch (ParserConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SAXException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "null";
	}
	public static void main(String[] args) {
		
	}
}
